package com.example.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.example.pojo.Course;
import com.example.pojo.CourseOption;
import com.example.pojo.Student;
import com.example.pojo.Teacher;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

public class DatabaseHelper extends SQLiteOpenHelper {
    public static final String DATABASE_NAME = "UserDB";
    private static final int DATABASE_VERSION = 1;
    private static final String TABLE_NAME = "user";
    private static final String COLUMN_ID = "id";
    private static final String COLUMN_USERNAME = "account";
    private static final String COLUMN_PASSWORD = "password";
    private static final String COLUMN_ROLE = "role";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 创建 student 表
        db.execSQL("CREATE TABLE student (" +
                "student_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "name TEXT NOT NULL, " +
                "age INTEGER, " +
                "college TEXT, " +
                "major TEXT, " +
                "password TEXT NOT NULL, " +
                "role TEXT CHECK(role IN ('student', 'teacher')) NOT NULL, " +
                "phone TEXT," +
                "grade TEXT)");

        // 创建 teacher 表
        db.execSQL("CREATE TABLE teacher (" +
                "teacher_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "name TEXT NOT NULL, " +
                "age INTEGER, " +
                "college TEXT, " +
                "job TEXT," +
                "office TEXT, " +
                "phone TEXT, " +
                "password TEXT NOT NULL, " +
                "role TEXT CHECK(role IN ('student', 'teacher')) NOT NULL)");

        // 创建 course 表
        db.execSQL("CREATE TABLE course (" +
                "course_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "course_name TEXT NOT NULL, " +
                "college TEXT NOT NULL," +
                "major TEXT NOT NULL," +
                "course_type TEXT CHECK(course_type IN ('选修', '必修'))," +
                "credits INTEGER, " +
                "semester INTEGER CHECK(semester BETWEEN 1 AND 8))");

        // 创建 teach 表
        db.execSQL("CREATE TABLE teach (" +
                "course_id INTEGER, " +
                "teacher_id INTEGER, " +
                "classroom TEXT, " +
                "week TEXT," +
                "num_week TEXT," +
                "start_time TEXT, " +
                "end_time TEXT, " +
                "PRIMARY KEY (course_id, teacher_id), " +
                "FOREIGN KEY (course_id) REFERENCES course(course_id), " +
                "FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id))");

        // 创建 enrollment 表
        db.execSQL("CREATE TABLE enrollment (" +
                "student_id INTEGER, " +
                "course_id INTEGER, " +
                "teacher_id INTEGER, " +
                "PRIMARY KEY (student_id, course_id, teacher_id), " +
                "FOREIGN KEY (student_id) REFERENCES student(student_id), " +
                "FOREIGN KEY (course_id) REFERENCES course(course_id), " +
                "FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id))");

        // 创建 user 视图
        db.execSQL("CREATE VIEW user AS " +
                "SELECT student_id AS account, password, role FROM student " +
                "UNION ALL " +
                "SELECT teacher_id AS account, password, role FROM teacher");
//        // 插入测试数据
        insertTestData(db);


    }


    public String getDatabaseName() {
        return DATABASE_NAME;
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.beginTransaction();
        try {
            if (oldVersion < 2) {
                db.execSQL("ALTER TABLE student ADD COLUMN grade TEXT");
            }
            ContentValues values = new ContentValues();
            values.put("grade", "21级");
            db.update("student", values, "student_id = ?", new String[]{String.valueOf(16211308)});
            // 提交事务
            db.setTransactionSuccessful();
        } finally {
            // 结束事务
            db.endTransaction();
        }
        db.close();
    }

    // 插入测试数据
    private void insertTestData(SQLiteDatabase db) {
        // 插入学生数据
        db.execSQL("INSERT INTO student (student_id, name, age, college, major, password, role, phone, grade) " +
                "VALUES (16211308, '张三', 20, '计算机科学与技术', '物联网工程', '123456', 'student', '13800138000', '21级')");

        db.execSQL("INSERT INTO student (student_id, name, age, college, major, password, role, phone, grade) " +
                "VALUES (16211309, '李四', 21, '计算机科学与技术', '计算机科学与技术', '123456', 'student', '13800138001', '21级')");

        db.execSQL("INSERT INTO student (student_id, name, age, college, major, password, role, phone, grade) " +
                "VALUES (16211310, '王五', 22, '计算机科学与技术', '物联网工程', '123456', 'student', '13800138002', '21级')");

        db.execSQL("INSERT INTO student (student_id, name, age, college, major, password, role, phone, grade) " +
                "VALUES (16211311, '赵六', 20, '计算机科学与技术', '网络安全', '123456', 'student', '13800138003', '21级')");

    // 插入教师数据
        db.execSQL("INSERT INTO teacher (teacher_id, name, age, college, office, phone, password, role, job) " +
                "VALUES (111111, '李老师', 35, '计算机科学与技术', 'A101', '13800138001', '123456', 'teacher', '院长')");

        db.execSQL("INSERT INTO teacher (teacher_id, name, age, college, office, phone, password, role, job) " +
                "VALUES (111112, '王老师', 40, '计算机科学与技术', 'A102', '13800138002', '123456', 'teacher', '主任')");

        db.execSQL("INSERT INTO teacher (teacher_id, name, age, college, office, phone, password, role, job) " +
                "VALUES (111113, '张老师', 38, '计算机科学与技术', 'A103', '13800138003', '123456', 'teacher', '教授')");

        db.execSQL("INSERT INTO teacher (teacher_id, name, age, college, office, phone, password, role, job) " +
                "VALUES (111114, '陈老师', 45, '计算机科学与技术', 'A104', '13800138004', '123456', 'teacher', '讲师')");

        // 插入计算机科学与技术学院的课程
        db.execSQL("INSERT INTO course (course_id, course_name, credits, semester, college, major, course_type) " +
                "VALUES (1, '数据库原理', 3, 5, '计算机科学与技术', '计算机科学与技术', '必修')");

        db.execSQL("INSERT INTO course (course_id, course_name, credits, semester, college, major, course_type) " +
                "VALUES (2, '数据库设计', 3, 5, '计算机科学与技术', '物联网工程', '选修')");

        db.execSQL("INSERT INTO course (course_id, course_name, credits, semester, college, major, course_type) " +
                "VALUES (3, '操作系统', 4, 5, '计算机科学与技术', '网络安全', '必修')");

        db.execSQL("INSERT INTO course (course_id, course_name, credits, semester, college, major, course_type) " +
                "VALUES (4, '计算机网络', 3, 5, '计算机科学与技术', '计算机科学与技术', '必修')");

        db.execSQL("INSERT INTO course (course_id, course_name, credits, semester, college, major, course_type) " +
                "VALUES (5, '数据结构', 4, 5, '计算机科学与技术', '物联网工程', '必修')");

        // 插入商学院的课程
        db.execSQL("INSERT INTO course (course_id, course_name, credits, semester, college, major, course_type) " +
                "VALUES (6, '管理学原理', 2, 1, '商学院', '工商管理', '必修')");

        db.execSQL("INSERT INTO course (course_id, course_name, credits, semester, college, major, course_type) " +
                "VALUES (7, '市场营销学', 3, 4, '商学院', '市场营销', '选修')");

        db.execSQL("INSERT INTO course (course_id, course_name, credits, semester, college, major, course_type) " +
                "VALUES (8, '会计学基础', 3, 2, '商学院', '会计学', '必修')");

        // 插入文学院的课程
        db.execSQL("INSERT INTO course (course_id, course_name, credits, semester, college, major, course_type) " +
                "VALUES (9, '现代汉语', 2, 1, '文学院', '汉语言文学', '必修')");

        db.execSQL("INSERT INTO course (course_id, course_name, credits, semester, college, major, course_type) " +
                "VALUES (10, '新闻写作', 3, 3, '文学院', '新闻学', '选修')");

        // 插入理学院的课程
        db.execSQL("INSERT INTO course (course_id, course_name, credits, semester, college, major, course_type) " +
                "VALUES (11, '高等数学', 4, 1, '理学院', '数学与应用数学', '必修')");

        db.execSQL("INSERT INTO course (course_id, course_name, credits, semester, college, major, course_type) " +
                "VALUES (12, '量子力学', 3, 4, '理学院', '物理学', '必修')");

        db.execSQL("INSERT INTO course (course_id, course_name, credits, semester, college, major, course_type) " +
                "VALUES (13, '有机化学', 3, 3, '理学院', '化学', '选修')");

        // 插入工程学院的课程
        db.execSQL("INSERT INTO course (course_id, course_name, credits, semester, college, major, course_type) " +
                "VALUES (14, '机械设计基础', 4, 3, '工程学院', '机械工程', '必修')");

        db.execSQL("INSERT INTO course (course_id, course_name, credits, semester, college, major, course_type) " +
                "VALUES (15, '电路分析', 3, 2, '工程学院', '电气工程', '必修')");

        db.execSQL("INSERT INTO course (course_id, course_name, credits, semester, college, major, course_type) " +
                "VALUES (16, '结构力学', 4, 4, '工程学院', '土木工程', '选修')");

        // 插入医学院的课程
        db.execSQL("INSERT INTO course (course_id, course_name, credits, semester, college, major, course_type) " +
                "VALUES (17, '人体解剖学', 4, 2, '医学院', '临床医学', '必修')");

        db.execSQL("INSERT INTO course (course_id, course_name, credits, semester, college, major, course_type) " +
                "VALUES (18, '药理学', 3, 3, '医学院', '药学', '选修')");

        // 插入艺术学院的课程
        db.execSQL("INSERT INTO course (course_id, course_name, credits, semester, college, major, course_type) " +
                "VALUES (19, '音乐理论', 2, 1, '艺术学院', '音乐学', '必修')");

        db.execSQL("INSERT INTO course (course_id, course_name, credits, semester, college, major, course_type) " +
                "VALUES (20, '素描基础', 3, 2, '艺术学院', '美术学', '选修')");

        // 插入法学院的课程
        db.execSQL("INSERT INTO course (course_id, course_name, credits, semester, college, major, course_type) " +
                "VALUES (21, '民法总论', 3, 2, '法学院', '法学', '必修')");

        db.execSQL("INSERT INTO course (course_id, course_name, credits, semester, college, major, course_type) " +
                "VALUES (22, '知识产权法', 3, 4, '法学院', '知识产权', '选修')");

        // 插入教育学院的课程
        db.execSQL("INSERT INTO course (course_id, course_name, credits, semester, college, major, course_type) " +
                "VALUES (23, '教育心理学', 3, 3, '教育学院', '教育学', '必修')");

        db.execSQL("INSERT INTO course (course_id, course_name, credits, semester, college, major, course_type) " +
                "VALUES (24, '儿童发展', 2, 2, '教育学院', '学前教育', '选修')");

        // 插入农学院的课程
        db.execSQL("INSERT INTO course (course_id, course_name, credits, semester, college, major, course_type) " +
                "VALUES (25, '作物栽培学', 3, 3, '农学院', '农学', '必修')");

        db.execSQL("INSERT INTO course (course_id, course_name, credits, semester, college, major, course_type) " +
                "VALUES (26, '园艺学原理', 3, 4, '农学院', '园艺学', '选修')");

        // 插入授课数据
        db.execSQL("INSERT INTO teach (course_id, teacher_id, classroom, start_time, end_time, week, num_week) " +
                "VALUES (1, 111111, '计算机楼B201', '08:00', '11:40', '周一', '1-10周')");

        db.execSQL("INSERT INTO teach (course_id, teacher_id, classroom, start_time, end_time, week, num_week) " +
                "VALUES (2, 111112, '计算机楼B202', '13:30', '15:10', '周二', '1-10周')");

        db.execSQL("INSERT INTO teach (course_id, teacher_id, classroom, start_time, end_time, week, num_week) " +
                "VALUES (3, 111113, '计算机楼B203', '10:00', '11:40', '周三', '1-10周')");

        db.execSQL("INSERT INTO teach (course_id, teacher_id, classroom, start_time, end_time, week, num_week) " +
                "VALUES (4, 111114, '计算机楼B204', '15:30', '17:10', '周四', '1-10周')");

        db.execSQL("INSERT INTO teach (course_id, teacher_id, classroom, start_time, end_time, week, num_week) " +
                "VALUES (5, 111111, '计算机楼B205', '08:00', '11:40', '周五', '1-10周')");

// 插入选课数据
        db.execSQL("INSERT INTO enrollment (student_id, course_id, teacher_id) " +
                "VALUES (16211308, 1, 111111)");

        db.execSQL("INSERT INTO enrollment (student_id, course_id, teacher_id) " +
                "VALUES (16211309, 2, 111112)");

        db.execSQL("INSERT INTO enrollment (student_id, course_id, teacher_id) " +
                "VALUES (16211310, 3, 111113)");

        db.execSQL("INSERT INTO enrollment (student_id, course_id, teacher_id) " +
                "VALUES (16211311, 4, 111114)");

        db.execSQL("INSERT INTO enrollment (student_id, course_id, teacher_id) " +
                "VALUES (16211308, 5, 111111)");

        db.execSQL("INSERT INTO enrollment (student_id, course_id, teacher_id) " +
                "VALUES (16211309, 3, 111113)");

        db.execSQL("INSERT INTO enrollment (student_id, course_id, teacher_id) " +
                "VALUES (16211310, 4, 111114)");
    }

    // 验证用户
    public boolean validateUser(Integer username, String password, String role) {
        SQLiteDatabase db = this.getReadableDatabase();
        String query = "SELECT * FROM " + TABLE_NAME + " WHERE "
                + COLUMN_USERNAME + " = ? AND "
                + COLUMN_PASSWORD + " = ? AND "
                + COLUMN_ROLE + " = ?";
        Cursor cursor = db.rawQuery(query, new String[]{String.valueOf(username), password, role});
        boolean isValid = cursor.getCount() > 0;
        cursor.close();
        return isValid;
    }

    //find info
    public Cursor getUserInfo(int studentId) {
        SQLiteDatabase db = this.getReadableDatabase();
        String query = "SELECT * FROM student WHERE student_id = ?";
        return db.rawQuery(query, new String[]{String.valueOf(studentId)});
    }

    public void updatePhone(int Id, String phone, Integer flag) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.beginTransaction();
        if (flag == 0) {
            try {
                ContentValues values = new ContentValues();
                values.put("phone", phone);
                db.update("student", values, "student_id = ?", new String[]{String.valueOf(Id)});
                // 提交事务
                db.setTransactionSuccessful();
            } finally {
                // 结束事务
                db.endTransaction();
            }
        } else {
            try {
                ContentValues values = new ContentValues();
                values.put("phone", phone);
                db.update("teacher", values, "teacher_id = ?", new String[]{String.valueOf(Id)});
                // 提交事务
                db.setTransactionSuccessful();
            } finally {
                // 结束事务
                db.endTransaction();
            }
        }

        db.close();
    }

    public void enrollCourse(Integer courseId, Integer teacherId, Integer studentId) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("course_id", courseId);
        values.put("teacher_id", teacherId);
        values.put("student_id", studentId);
        db.insert("enrollment", null, values);
        db.close();
    }

    public void unenrollCourse(Integer courseId, Integer teacherId, Integer studentId) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete("enrollment", "course_id = ? AND teacher_id = ? AND student_id = ?",
                new String[]{String.valueOf(courseId), String.valueOf(teacherId), String.valueOf(studentId)});
        db.close();
    }

    public boolean isEnrolled(Integer courseId, Integer teacherId, Integer studentId) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query("enrollment", null, "course_id = ? AND teacher_id = ? AND student_id = ?",
                new String[]{String.valueOf(courseId), String.valueOf(teacherId), String.valueOf(studentId)}, null, null, null);
        boolean isEnrolled = cursor.getCount() > 0;
        cursor.close();
        return isEnrolled;
    }
    private String getStudentCollege(String studentId) {
        SQLiteDatabase db = this.getReadableDatabase();
        String query = "SELECT college FROM student WHERE student_id = ?";
        Cursor cursor = db.rawQuery(query, new String[]{studentId});

        String college = "";
        if (cursor.moveToFirst()) {
            college = cursor.getString(0); // 获取学生的学院
        }

        cursor.close();
        return college;
    }

    private String getStudentMajor(String studentId) {
        SQLiteDatabase db = this.getReadableDatabase();
        String query = "SELECT major FROM student WHERE student_id = ?";
        Cursor cursor = db.rawQuery(query, new String[]{studentId});

        String major = "";
        if (cursor.moveToFirst()) {
            major = cursor.getString(0); // 获取学生的专业
        }

        cursor.close();
        return major;
    }

    public Cursor getAllCourses(String studentId) {
        SQLiteDatabase db = this.getReadableDatabase();
        // 查询学生的学院
        String studentCollege = getStudentCollege(studentId);
        String studentMajor = getStudentMajor(studentId);

        // SQL 查询语句
        String query = "SELECT " +
                "course.course_id, " +
                "teach.teacher_id, " +
                "course.course_name, " +
                "teacher.name AS teacher_name, " +
                "teach.classroom, " +
                "teach.week, " +
                "teach.num_week, " +
                "teach.start_time, " +
                "teach.end_time, " +
                "course.credits, " +
                "course.college " +
                "FROM course " +
                "INNER JOIN teach ON course.course_id = teach.course_id " +
                "INNER JOIN teacher ON teach.teacher_id = teacher.teacher_id " +
                "WHERE (course.college = ? AND course.major = ?) OR course.college = '校公选'"; // 条件：课程学院与学生学院相同，或者课程是校公选

        // 执行查询
        return db.rawQuery(query, new String[]{studentCollege,studentMajor});
    }


    // 获取教师信息
    public Teacher getTeacherInfo(int teacherId) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM teacher WHERE teacher_id = ?", new String[]{String.valueOf(teacherId)});
        if (cursor.moveToFirst()) {
            Teacher teacher = new Teacher();
            int teacherIdIndex = cursor.getColumnIndex("teacher_id");
            int teacherNameIndex = cursor.getColumnIndex("name");
            int ageIndex = cursor.getColumnIndex("age");
            int collegeIndex = cursor.getColumnIndex("college");
            int officeIndex = cursor.getColumnIndex("office");
            int phoneIndex = cursor.getColumnIndex("phone");
            int jobIndex = cursor.getColumnIndex("job");
            // 设置教师信息
            if (teacherIdIndex != -1) {
                teacher.setTeacherId(cursor.getString(teacherIdIndex));
            }
            if (teacherNameIndex != -1) {
                teacher.setTeacherName(cursor.getString(teacherNameIndex));
            }
            if (ageIndex != -1) {
                teacher.setAge(cursor.getInt(ageIndex));
            }
            if (collegeIndex != -1) {
                teacher.setCollege(cursor.getString(collegeIndex));
            }
            if (officeIndex != -1) {
                teacher.setOffice(cursor.getString(officeIndex));
            }
            if (phoneIndex != -1) {
                teacher.setPhone(cursor.getString(phoneIndex));
            }
            if (jobIndex != -1) {
                teacher.setJob(cursor.getString(jobIndex));
            }
            cursor.close();
            return teacher;
        }
        cursor.close();
        return null;
    }


    public String getJobFromDatabase(Integer teacherId) {
        String job = null;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT job FROM teacher WHERE teacher_id = ?", new String[]{String.valueOf(teacherId)});
        if (cursor.moveToFirst()) {
            int index = cursor.getColumnIndex("job");
            if (index != -1)
                job = cursor.getString(index);
        }
        cursor.close();
        return job;
    }

    /**
     * 插入学生信息
     *
     * @param student 学生对象
     * @return 插入是否成功
     */
    public boolean insertStudent(Student student) {
        SQLiteDatabase db = this.getReadableDatabase();
        ContentValues values = new ContentValues();
        values.put("student_id", student.getStudentId());
        values.put("name", student.getName());
        values.put("age", student.getAge());
        values.put("college", student.getCollege());
        values.put("major", student.getMajor());
        values.put("password", student.getPassword());
        values.put("role", student.getRole());
        values.put("phone", student.getPhone());
        values.put("grade", student.getGrade());

        long result = db.insert("student", null, values);
        return result != -1; // 如果插入成功，返回 true
    }

    public List<Student> getStudentsByFilter(String college, String major, String grade) {
        List<Student> students = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();

        // 构建查询条件
        StringBuilder query = new StringBuilder("SELECT student_id, name, age, college, major, phone, grade FROM student WHERE 1=1");
        List<String> args = new ArrayList<>();

        if (college != null && !college.isEmpty()) {
            query.append(" AND college = ?");
            args.add(college);
        }
        if (major != null && !major.isEmpty()) {
            query.append(" AND major = ?");
            args.add(major);
        }
        if (grade != null && !grade.isEmpty()) {
            query.append(" AND grade = ?");
            args.add(grade);
        }

        // 执行查询
        Cursor cursor = db.rawQuery(query.toString(), args.toArray(new String[0]));

        if (cursor.moveToFirst()) {
            do {

                Student student = new Student(
                        cursor.getString(0), // student_id
                        cursor.getString(1), // name
                        cursor.getInt(2),    // age
                        cursor.getString(3), // college
                        cursor.getString(4), // major
                        cursor.getString(5), // phone
                        cursor.getString(6)  // grade
                );
                students.add(student);
            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();
        return students;
    }

    public List<Student> getAllStudents() {
        List<Student> students = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT student_id, name, age, college, major, phone, grade FROM student ", null);

        if (cursor.moveToFirst()) {
            do {
                Student student = new Student(
                        cursor.getString(0), // student_id
                        cursor.getString(1), // name
                        cursor.getInt(2),    // age
                        cursor.getString(3), // college
                        cursor.getString(4), // major
                        cursor.getString(5), // phone
                        cursor.getString(6)  // grade
                );
                students.add(student);
            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();
        return students;
    }

    public void deleteStudent(String studentId) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete("student", "student_id = ?", new String[]{studentId});
        db.close();
    }

    public void deleteTeacher(String teacherId) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete("teach", "teacher_id = ?", new String[]{teacherId});
        db.delete("teacher", "teacher_id = ?", new String[]{teacherId});
        db.close();
    }

    public List<Teacher> getAllTeachers() {
        List<Teacher> teachers = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();

        // 查询所有教师
        Cursor cursor = db.rawQuery("SELECT teacher_id, name, college, office, phone, job, age FROM teacher", null);

        if (cursor.moveToFirst()) {
            do {
                Teacher teacher = new Teacher(
                        cursor.getString(0),        // teacher_id
                        cursor.getString(1),     // teacher_name
                        cursor.getString(2),     // college
                        cursor.getString(3),     // office
                        cursor.getString(4),     // phone
                        cursor.getString(5),     // job
                        cursor.getInt(6)         // age
                );
                teachers.add(teacher);
            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();
        return teachers;
    }

    public Collection<? extends Teacher> getTeachersByFilter(String college, String job) {
        List<Teacher> teachers = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();

        // 构建查询条件
        StringBuilder query = new StringBuilder("SELECT teacher_id, name, college, office, phone, job, age FROM teacher WHERE 1=1");
        List<String> args = new ArrayList<>();

        if (college != null && !college.isEmpty()) {
            query.append(" AND college = ?");
            args.add(college);
        }
        if (job != null && !job.isEmpty()) {
            query.append(" AND job = ?");
            args.add(job);
        }

        // 执行查询
        Cursor cursor = db.rawQuery(query.toString(), args.toArray(new String[0]));

        if (cursor.moveToFirst()) {
            do {
                Teacher teacher = new Teacher(
                        cursor.getString(0),        // teacher_id
                        cursor.getString(1),     // teacher_name
                        cursor.getString(2),     // college
                        cursor.getString(3),     // office
                        cursor.getString(4),     // phone
                        cursor.getString(5),     // job
                        cursor.getInt(6)         // age
                );
                teachers.add(teacher);
            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();
        return teachers;
    }

    public boolean insertTeacher(Teacher teacher) {
        SQLiteDatabase db = this.getReadableDatabase();
        ContentValues values = new ContentValues();
        values.put("teacher_id", teacher.getTeacherId());
        values.put("name", teacher.getTeacherName());
        values.put("age", teacher.getAge());
        values.put("college", teacher.getCollege());
        values.put("office", teacher.getOffice());
        values.put("password", teacher.getPassword());
        values.put("role", "teacher");
        values.put("phone", teacher.getPhone());
        values.put("job", teacher.getJob());

        long result = db.insert("teacher", null, values);
        return result != -1; // 如果插入成功，返回 true
    }

    public List<Course> getCourses() {
        List<Course> courses = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();

        // 查询所有课程
        Cursor cursor = db.rawQuery("SELECT course_id, course_name, college, major, course_type, credits, semester FROM course", null);

        if (cursor.moveToFirst()) {
            do {
                Course course = new Course(
                        cursor.getString(0),        // course_id
                        cursor.getString(1),        // course_name
                        cursor.getString(2),        // college
                        cursor.getString(3),        // major
                        cursor.getString(4),        // course_type
                        cursor.getInt(5),           // credits
                        cursor.getString(6)            // semester
                );
                courses.add(course);
            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();
        return courses;
    }

    public void deleteCourse(String courseId) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete("teach", "course_id = ?", new String[]{courseId});
        db.delete("course", "course_id = ?", new String[]{courseId});
        db.close();
    }

    public boolean insertCourse(Course course) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("course_id", course.getCourseId());          // 课程ID
        values.put("course_name", course.getCourseName());      // 课程名称
        values.put("college", course.getCollege());             // 学院
        values.put("major", course.getMajor());                 // 专业
        values.put("course_type", course.getCourseType());      // 课程类型
        values.put("credits", course.getCredits());             // 学分
        values.put("semester", course.getSemester());           // 学期

        long result = db.insert("course", null, values);
        db.close();
        return result != -1; // 如果插入成功，返回 true；否则返回 false
    }

    public List<Course> getCoursesByFilter(String college, String major, String semester, String courseType) {
        List<Course> courses = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();

        // 构建查询条件
        StringBuilder query = new StringBuilder("SELECT course_id, course_name, college, major, course_type, credits, semester FROM course WHERE 1=1");
        List<String> args = new ArrayList<>();
        if (college != null && !college.isEmpty()) {
            query.append(" AND college = ?");
            args.add(college);
        }
        if (major != null && !major.isEmpty()) {
            query.append(" AND major = ?");
            args.add(major);
        }
        if (semester != null && !semester.isEmpty()) {
            query.append(" AND semester = ?");
            args.add(String.valueOf(semester));
        }
        if (courseType != null && !courseType.isEmpty()) {
            query.append(" AND course_type = ?");
            args.add(String.valueOf(courseType));
        }

        // 执行查询
        Cursor cursor = db.rawQuery(query.toString(), args.toArray(new String[0]));
        if (cursor.moveToFirst()) {
            do {
                Course course = new Course(
                        cursor.getString(0),        // course_id
                        cursor.getString(1),        // course_name
                        cursor.getString(2),        // college
                        cursor.getString(3),        // major
                        cursor.getString(4),        // course_type
                        cursor.getInt(5),           // credits
                        cursor.getString(6)            // semester
                );
                courses.add(course);
            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();
        return courses;
    }

    public List<CourseOption> getCourseOptionsByFilter(String college, String major, String courseType) {
        List<CourseOption> courseOptions = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();

        // 构建查询条件
        StringBuilder query = new StringBuilder("SELECT c.course_id, c.course_name, c.college, c.major, c.course_type, c.credits, c.semester, " +
                "t.teacher_id, t.name AS teacher_name, t.college AS teacher_college, t.office, t.phone, t.job, t.age, " +
                "te.start_time, te.end_time, te.week, te.num_week " +
                "FROM course c " +
                "JOIN teach te ON c.course_id = te.course_id " +
                "JOIN teacher t ON te.teacher_id = t.teacher_id " +
                "WHERE 1=1");
        List<String> args = new ArrayList<>();
        if (college != null && !college.isEmpty()) {
            query.append(" AND c.college = ?");
            args.add(college);
        }
        if (major != null && !major.isEmpty()) {
            query.append(" AND c.major = ?");
            args.add(major);
        }
        if (courseType != null && !courseType.isEmpty()) {
            query.append(" AND c.course_type = ?");
            args.add(courseType);
        }

        // 执行查询
        Cursor cursor = db.rawQuery(query.toString(), args.toArray(new String[0]));
        if (cursor.moveToFirst()) {
            do {
                // 映射课程信息
                Course course = new Course(
                        cursor.getString(0),        // course_id
                        cursor.getString(1),        // course_name
                        cursor.getString(2),        // college
                        cursor.getString(3),        // major
                        cursor.getString(4),        // course_type
                        cursor.getInt(5),           // credits
                        cursor.getString(6)         // semester
                );

                // 映射教师信息
                Teacher teacher = new Teacher(
                        cursor.getString(7),        // teacher_id
                        cursor.getString(8),        // teacher_name
                        cursor.getString(9),        // teacher_college
                        cursor.getString(10),       // office
                        cursor.getString(11),       // phone
                        cursor.getString(12),       // job
                        cursor.getInt(13)           // age
                );

                // 映射授课时间信息
                String startTime = cursor.getString(14); // start_time
                String endTime = cursor.getString(15);   // end_time
                String week = cursor.getString(16);     // week
                String numWeek = cursor.getString(17);  // num_week

                // 创建 CourseOption 对象
                CourseOption courseOption = new CourseOption();
                courseOption.setCourse(course);
                courseOption.setTeacher(teacher);
                courseOption.setStartTime(startTime);
                courseOption.setEndTime(endTime);
                courseOption.setWeek(week);
                courseOption.setNumWeek(numWeek);

                courseOptions.add(courseOption);
            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();
        return courseOptions;
    }

    public List<CourseOption> getCourseOptions() {
        List<CourseOption> courseOptions = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        // 查询所有课程选项
        String query = "SELECT c.course_id, c.course_name, c.college, c.major, c.course_type, c.credits, c.semester, " +
                "t.teacher_id, t.name AS teacher_name, t.college AS teacher_college, t.office, t.phone, t.job, t.age, " +
                "te.start_time, te.end_time, te.week, te.num_week " +
                "FROM course c " +
                "JOIN teach te ON c.course_id = te.course_id " +
                "JOIN teacher t ON te.teacher_id = t.teacher_id";

        Cursor cursor = db.rawQuery(query, null);
        if (cursor.moveToFirst()) {
            do {
                // 映射课程信息
                Course course = new Course(
                        cursor.getString(0),        // course_id
                        cursor.getString(1),        // course_name
                        cursor.getString(2),        // college
                        cursor.getString(3),        // major
                        cursor.getString(4),        // course_type
                        cursor.getInt(5),           // credits
                        cursor.getString(6)         // semester
                );

                // 映射教师信息
                Teacher teacher = new Teacher(
                        cursor.getString(7),        // teacher_id
                        cursor.getString(8),        // teacher_name
                        cursor.getString(9),        // teacher_college
                        cursor.getString(10),       // office
                        cursor.getString(11),       // phone
                        cursor.getString(12),       // job
                        cursor.getInt(13)           // age
                );

                // 映射授课时间信息
                String startTime = cursor.getString(14); // start_time
                String endTime = cursor.getString(15);   // end_time
                String week = cursor.getString(16);     // week
                String numWeek = cursor.getString(17);  // num_week

                // 创建 CourseOption 对象
                CourseOption courseOption = new CourseOption();
                courseOption.setCourse(course);
                courseOption.setTeacher(teacher);
                courseOption.setStartTime(startTime);
                courseOption.setEndTime(endTime);
                courseOption.setWeek(week);
                courseOption.setNumWeek(numWeek);

                courseOptions.add(courseOption);
            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();
        return courseOptions;
    }

    public void deleteCourseInTeach(String courseId, String teacherId) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete("teach", "course_id = ? AND teacher_id = ?", new String[]{courseId, teacherId});
        db.close();
    }
}